package com.headcaselabs.lb.director;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.List;

import com.headcaselabs.lb.common.Server;
/**
 * Handles the DB access for the LB director.
 * @author Florian Leibert
 *
 */ 
class DBTools {
	Connection									conn		= null;

	protected  String	dbHost;

	protected  String	dbUser;

	protected  String	dbPwd;

	protected  String	db;
	

	//public static final String	SERVER_SET_TABLE		= "servers";
	
	private static final DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");


	
	/**
	 * Full Constructor
	 * @param dbhost
	 * @param dbuser
	 * @param dbpass
	 * @param db
	 */
	public DBTools(String dbHost, String dbUser, String dbPwd, String db) throws ClassNotFoundException,IllegalAccessException,InstantiationException,SQLException {
		this.dbHost = dbHost;
		this.dbUser = dbUser;
		this.dbPwd = dbPwd;
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		conn = DriverManager.getConnection("jdbc:mysql://" + dbHost + "/" + db
					+ "?user=" + dbUser + "&password=" + dbPwd);
		
	}
	
	/**
	 * Gets the group size
	 * @param groupId
	 * @return
	 */
	public int getGroupSize(String groupName) {
		Statement stmt = null;
		ResultSet rs = null;
		int size = -1;
		try {
			stmt = conn.createStatement();
			String query = "SELECT COUNT(*) FROM "+DirectorImpl.SERVER_TABLE+" where group_name='"+groupName+"'";
			rs = stmt.executeQuery(query);
      rs.next();
      size = rs.getInt(1);
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
		
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { 
					rs = null;
					System.out.println("SQL Error:"+sqlEx);
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException sqlEx) { 
						stmt = null;
					}
				}
			}
		}
		return size;
	}
	
	/**
	 * Checks if there are updates available for a group
	 * @param groupId
	 * @param modified
	 * @return
	 */
	public boolean isUpdated(String groupName, long modified) {
		Statement stmt = null;
		ResultSet rs = null;
		Date lastModified = new Date(modified);
		int size = -1;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT COUNT(*) FROM "+DirectorImpl.SERVER_TABLE+ " where group_name = '"+groupName+"' AND modified > '"+dateFormat.format(lastModified)+"'");
      rs.next();
      size = rs.getInt(1);
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
		
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { 
					rs = null;

					System.out.println("SQL Error:"+sqlEx);
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException sqlEx) { 
						stmt = null;

						System.out.println("SQL Error:"+sqlEx);
					}
				}
			}
		}
		if (size>0) {
			return true;
		}
		return false;
	}

	/**
	 * Load Group
	 * @param groupId
	 */
	public List<Server> loadGroup(String groupName) {
		Statement stmt = null;
		ResultSet rs = null;
		List<Server> servers = new LinkedList<Server>();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM "+DirectorImpl.SERVER_TABLE+ " where group_name = '"+groupName+"'");
			String nGroupName, nMode, nArg;
			Server server;
			while (rs.next()) {
				nGroupName = rs.getString("group_name");
			  nMode = rs.getString("record_type");
			  nArg = rs.getString("arg");
			  server = new Server(nMode,nArg,nGroupName);
			  servers.add(server);
			  System.out.println("Retrieved Record: LbName:"+nGroupName+ " type:" + nMode+ " arg:"+nArg);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
		
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { 
					rs = null;

					System.out.println("SQL Error:"+sqlEx);
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException sqlEx) {
						stmt = null;

						System.out.println("SQL Error:"+sqlEx);
					}
				}
			}
		}
		return servers;
	}
	
	/**
	 * Load All Groups
	 * @param groupId
	 */
	public Hashtable<String,List<Server>> loadAllGroups() {
		Statement stmt = null;
		ResultSet rs = null;
		Hashtable<String, List<Server>> servers = new Hashtable<String,List<Server>>();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM "+DirectorImpl.SERVER_TABLE);
			String nGroupName, nMode, nArg;
			Server srv;
			List<Server> serverList;
			while (rs.next()) {
				nGroupName = rs.getString("group_name");
			  nMode = rs.getString("record_type");
			  nArg = rs.getString("arg");
			  srv = new Server(nMode,nArg,nGroupName);
			  serverList = servers.get(nGroupName);
			  if (serverList ==null) {
			  	serverList = new LinkedList<Server>();
			  	servers.put(nGroupName, serverList);
			  }
			  serverList.add(srv);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
		
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { 
					rs = null;

					System.out.println("SQL Error:"+sqlEx);
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException sqlEx) {
						stmt = null;

						System.out.println("SQL Error:"+sqlEx);
					}
				}
			}
		}
		return servers;
	}
	
	public void insertNode(String groupName, String type, String arg, long modified) {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate("INSERT INTO "+DirectorImpl.SERVER_TABLE+ "(group_name,record_type,arg,modified) values ('"+groupName+"','"+type+"','"+arg+"','"+dateFormat.format(modified)+"')");
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
		
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { 
					rs = null;

					System.out.println("SQL Error:"+sqlEx);
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException sqlEx) {
						stmt = null;

						System.out.println("SQL Error:"+sqlEx);
					}
				}
			}
		}
		
	}
}
